<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace App;
class SqliteDatabase extends SQLDatabase {
    private $dbh, $persistent;

    protected static $sql_var = array(
        'current_date_utc'       => "date('now','utc')",
        'current_date_local'     => "date('now')",
        'current_datetime_utc'   => "datetime('now','utc')",
        'current_datetime_local' => "datetime('now')"
    );

    protected function _connect () {
        global $trace;
        if ($trace) trace_entry();
        if (! isset($this->dbh)) {
            $filename = $this->_filename();
            $mode = 0640;
            if (isset($this->dboptions['mode'])) $mode = $this->dboptions['mode'] + 0;
            $errmsg = null;
            #echo "_connect($filename, $mode) : sqlite\n";
            if (isset($this->dboptions['persistent']) && $this->dboptions['persistent']) {
                $this->dbh = sqlite_popen($filename, $mode, $errmsg);
                $this->persistent = 1;
            }
            else {
                $this->dbh = sqlite_open($filename, $mode, $errmsg);
                $this->persistent = 0;
            }
            #echo "_connect() : connected [$this->dbh]\n";
            if (isset($errmsg)) throw new \Exception('Error connecting to sqlite database: $errmsg.');
            if (!$this->dbh) throw new \Exception('Could not connect to database.');
        }
        if ($trace) trace_exit();
    }

    protected function _disconnect () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dbh) && !$this->persistent) {
            sqlite_close($this->dbh);
        }
        if ($trace) trace_exit();
    }

    private function _filename() {
        global $trace;
        if ($trace) trace_entry();
        $options = $this->context->options();
        $prefix  = $options['prefix'];
        $filename = "$prefix/appdata/Database/$this->name/this->name.sqlite3";
        if (! file_exists($filename)) {
            if (! is_dir("$prefix/appdata")) {
                mkdir("$prefix/appdata");
            }
            if (! is_dir("$prefix/appdata/Database")) {
                mkdir("$prefix/appdata/Database");
            }
            if (! is_dir("$prefix/appdata/Database/$this->name")) {
                mkdir("$prefix/appdata/Database/$this->name");
            }
        }
        if ($trace) trace_exit($filename);
        return $filename;
    }

    # NOTE: Sqlite does not support multiple schemas, so the assumed schema is always "main"
    protected function _assumed_schema_name() {
        global $trace;
        if ($trace) trace_entry();
        $schema_name = 'main';
        if ($trace) trace_exit($schema_name);
        return $schema_name;
    }

    # NOTE: Sqlite does not support multiple schemas, so the assumed schema is always "main"
    protected function _get_schema_defs_from_db () {
        global $trace;
        if ($trace) trace_entry();
        $schema_defs = Array('main' => Array('schema_name' => 'main', 'physical_ind' => 'Y'));
        if ($trace) trace_exit($schema_defs);
        return($schema_defs);
    }

    protected function _get_table_names ($schema_name = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema_name)) $schema_name = $this->schema_name;
        $sql = "SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1";
        #$sql = "SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1";
        $tables = $this->get_column($sql);
        if ($trace) trace_exit($tables);
        return($tables);
    }

    protected function _get_native_column_defs ($table, $schema_name = null) {
        global $trace;
        if ($trace) trace_entry();
        if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $table, $matches)) {
            $schema_name  = $matches[1];
            $fulltable = $table;
            $table     = $matches[2];
        }
        else {
            if (!isset($schema_name)) $schema_name = $this->schema_name;
            $fulltable = "$schema_name.$table";
        }
        $column_defs = sqlite_fetch_column_types($table, $this->dbh, SQLITE_ASSOC);
        if ($trace) trace_exit($column_defs);
        return($column_defs);
    }

    protected function _get_select_sth ($table, $params = null, $columns = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $sql  = $this->_get_select_sql($table, $params, $columns, $options);
        if ($debug_sql) fwrite($debug_fh, "<!-- DEBUG_SQL:\n" . $sql . " -->\n");
        $sth = sqlite_query($this->dbh, $sql);
        if (!$sth) {
            $message = 'Error in SQL: [' . mysql_errno() . '] ' . mysql_error();
            throw new \Exception($message . ' : ' . $sql);
        }
        if ($trace) trace_exit($sth);
        return($sth);
    }

    public function get_rows ($table, $params = null, $columns = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options);
        $rows = sqlite_fetch_all($query, SQLITE_NUM);
        if ($trace) trace_exit(count($rows).' rows');
        return($rows);
    }

    public function get_hashes ($table, $params = null, $columns = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options);
        $rows = sqlite_fetch_all($query, SQLITE_ASSOC);
        if ($trace) trace_exit(count($rows).' rows');
        return($rows);
    }

    public function get_column ($table, $params = null, $column = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options);
        $rows = sqlite_fetch_all($query, SQLITE_NUM);
        $column_data = array();
        foreach ($rows as $row) {
            $column_data[] = $row[0];
        }
        if ($trace) trace_exit($column_data);
        return($column_data);
    }

    protected function _make_limit_clause ($table, $options) {
        global $trace;
        if ($trace) trace_entry();
        $limit_clause = '';
        if (isset($options['limit']) && $options['limit']) {
            $limit_clause = "\nlimit " . $options['limit'];
            if (isset($options['offset']) && $options['offset'] > 0) {
                $limit_clause .= " offset " . $options['offset'];
            }
        }
        if ($trace) trace_exit($limit_clause);
        return($limit_clause);
    }

    public function _quote ($str_value) {
        $str_value = sqlite_escape_string($str_value);
        return("'" . $str_value . "'");
    }

    public function date_literal ($year, $month, $day) {
        return("'$year-$month-$day'");
    }

    public function datetime_literal ($year, $month, $day, $hours = '00', $min = '00', $sec = '00') {
        return("'$year-$month-$day $hours:$min:$sec'");
    }

    public function relative_date_literal ($days, $base_date = null) {
        if ($base_date == null) return("date('now','$days days')");
        else                    return("date('$base_date','$days days')");
    }

    public function relative_datetime_literal ($days, $hours = '00', $min = '00', $sec = '00', $base_datetime = null) {
        $seconds = $hours*3600 + $min*60 + $sec;
        if ($days < 0) $seconds = -$seconds;
        if ($days == intval($days)) {
            if ($base_datetime == null) return("datetime('now','$days days','$seconds seconds')");
            else                        return("datetime('$base_datetime','$days days','$seconds seconds')");
        }
        else {
            if ($base_datetime == null) return("datetime('now','$days days')");
            else                        return("datetime('$base_datetime','$days days')");
        }
    }
}

